/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package org.webshop.backend.dbchif;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.HashSet;
import java.util.LinkedList;
import java.util.Set;

import org.webshop.backend.data.Ingredient;
import org.webshop.backend.data.Product;
import org.webshop.backend.dbchif.sql.DbAccessStatement;
import org.webshop.backend.dbchif.sql.RawStatement;
import org.webshop.backend.dbchif.sql.StatementStore;
import org.webshop.backend.exceptions.DbAccessFailureException;
import org.webshop.backend.exceptions.NoConnectionAvailableException;

/**
 *
 * @author Martin Haubenwallner and Thomas Jahoda
 */
public class DbAccess {

    private static DbAccess instance;
    private DbAdvancedConnectionPool pool;

    private DbAccess() throws ClassNotFoundException {
        pool = DbAdvancedConnectionPool.getInstance();
    }

    public static DbAccess getInstance() {
        if (instance == null) {
            synchronized (DbAccess.class) {
                if (instance == null) {
                    try {
                        instance = new DbAccess();
                    } catch (ClassNotFoundException e) {
                        throw new RuntimeException("Could not find jdbc driver class", e);
                    }
                }
            }
        }
        return instance;
    }

    public void addIngredient(Ingredient ingredient) {
        AdvancedConnection connection = null;
        try {
            connection = fetchConnection();
            PreparedStatement statement = getPreparedStatement(DbAccessStatement.ADD_INGREDIGENT, connection);
            // designation, unit, amount
            statement.setString(1, ingredient.getDesignation());
            statement.setString(2, ingredient.getUnit());
            statement.setInt(3, ingredient.getAmount());
            statement.executeUpdate();
            ResultSet rs = statement.getGeneratedKeys();
            if (!rs.next()) {
                throw new DbAccessFailureException("No id for the new ingredient was generated");
            }
            int ingredientId = rs.getInt(1); // Ingredient_ID
            ingredient.setId(ingredientId);
        } catch (SQLException ex) {
            throw new DbAccessFailureException(ex.getMessage(), ex); // "Could not populate parameters of prepared statement"
        } finally {
            if (connection != null) {
                pool.releaseConnection(connection);
            }
        }
    }

    public void addProduct(Product product) {
        AdvancedConnection connection = null;
        try {
            connection = fetchConnection();
            PreparedStatement statement = getPreparedStatement(DbAccessStatement.ADD_PRODUCT, connection);
            // designation, unit, amount
            statement.setString(1, product.getDesignation());
            statement.setString(2, product.getUrl());

            statement.executeUpdate();
            ResultSet rs = statement.getGeneratedKeys();
            if (!rs.next()) {
                throw new DbAccessFailureException("No id for the new product was generated");
            }
            int productId = rs.getInt(1); // Ingredient_ID
            product.setId(productId);
        } catch (SQLException ex) {
            throw new DbAccessFailureException(ex.getMessage(), ex); // "Could not populate parameters of prepared statement"
        } finally {
            if (connection != null) {
                pool.releaseConnection(connection);
            }
        }
    }

    public void addIngredientToProduct(Product product, Ingredient ingredient) {
        AdvancedConnection connection = null;
        try {
            connection = fetchConnection();
            PreparedStatement statement = getPreparedStatement(DbAccessStatement.ADD_INGREDIENT_TO_PRODUCT, connection);

            statement.setInt(1, product.getId());
            statement.setInt(2, ingredient.getId());

            statement.executeUpdate();
        } catch (SQLException ex) {
            throw new DbAccessFailureException(ex.getMessage(), ex); // "Could not populate parameters of prepared statement"
        } finally {
            if (connection != null) {
                pool.releaseConnection(connection);
            }
        }
    }

    public void applyIngredientChanges(Ingredient ingredient) {
        AdvancedConnection connection = null;
        try {
            connection = fetchConnection();
            PreparedStatement statement = getPreparedStatement(DbAccessStatement.EDIT_INGREDIENT, connection);

            statement.setString(1, ingredient.getDesignation());
            statement.setString(2, ingredient.getUnit());
            statement.setInt(3, ingredient.getAmount());
            statement.setInt(4, ingredient.getId());
            statement.executeUpdate();
        } catch (SQLException ex) {
            throw new DbAccessFailureException(ex.getMessage(), ex); // "Could not populate parameters of prepared statement"
        } finally {
            if (connection != null) {
                pool.releaseConnection(connection);
            }
        }
    }

    public void removeIngredient(int id) {
        AdvancedConnection connection = null;
        try {
            connection = fetchConnection();
            PreparedStatement statement = getPreparedStatement(DbAccessStatement.REMOVE_INGREDIENT, connection);
            statement.setInt(1, id);
            statement.executeUpdate();
        } catch (SQLException ex) {
            throw new DbAccessFailureException(ex.getMessage(), ex); // "Could not populate parameters of prepared statement"
        } finally {
            if (connection != null) {
                pool.releaseConnection(connection);
            }
        }
    }

    public Set<Ingredient> getIngredients() {
        return getIngredients(false);
    }

    public Set<Ingredient> getIngredients(boolean sorted) {
        AdvancedConnection connection = null;
        try {
            connection = fetchConnection();
            // DbAccessStatement statementDef = DbAccessStatement.GET_INGREDIENTS_SORTED_BY_ID;
            DbAccessStatement statementDef = sorted ? DbAccessStatement.GET_INGREDIENTS_SORTED_BY_ID
                : DbAccessStatement.GET_INGREDIENTS;
            PreparedStatement statement = getPreparedStatement(statementDef, connection);
            ResultSet result = statement.executeQuery();
            HashSet<Ingredient> ingredients = new HashSet<Ingredient>();
            while (result.next()) {
                // Ingredient_ID, Designation, Unit, Amount
                int id = result.getInt("Ingredient_ID");
                String designation = result.getString("Designation");
                String unit = result.getString("Unit");
                int amount = result.getInt("Amount");
                Ingredient ingredient = new Ingredient(id, designation, unit, amount);
                ingredients.add(ingredient);
            }
            return ingredients;
        } catch (SQLException ex) {
            throw new DbAccessFailureException(ex.getMessage(), ex); // "Could not populate parameters of prepared statement"
        } finally {
            if (connection != null) {
                pool.releaseConnection(connection);
            }
        }
    }

    public Ingredient getIngredient(int id) {
        AdvancedConnection connection = null;
        try {
            connection = fetchConnection();
            PreparedStatement statement = getPreparedStatement(DbAccessStatement.GET_INGREDIENT, connection);
            statement.setInt(1, id);
            ResultSet result = statement.executeQuery();
            result.next();
            String designation = result.getString("Designation");
            String unit = result.getString("Unit");
            int amount = result.getInt("Amount");
            Ingredient ingredient = new Ingredient(id, designation, unit, amount);
            pool.releaseConnection(connection);
            return ingredient;
        } catch (SQLException ex) {
            throw new DbAccessFailureException(ex.getMessage(), ex); // "Could not populate parameters of prepared statement"
        } finally {
            if (connection != null) {
                pool.releaseConnection(connection);
            }
        }
    }

    public LinkedList<Product> getProducts() throws Exception {
        AdvancedConnection connection = null;
        try {
            connection = fetchConnection();
            PreparedStatement statement = getPreparedStatement(DbAccessStatement.SELECT_PRODUCT, connection);
            LinkedList<Product> prodList = new LinkedList<Product>();
            ResultSet rs = statement.executeQuery();
            while (rs.next()) {
                String pizzaName = rs.getString("designation");
                String pizzaUrl = rs.getString("url");
                double pizzaPrice = rs.getDouble("price");
                Product product = new Product(pizzaUrl, pizzaName, pizzaPrice);
                prodList.add(product);
            }
            return prodList;
        } catch (SQLException ex) {
            throw new DbAccessFailureException(ex.getMessage(), ex); // "Could not populate parameters of prepared statement"
        } finally {
            if (connection != null) {
                pool.releaseConnection(connection);
            }
        }
    }

    public LinkedList<String> getRestaurants() throws Exception {
        AdvancedConnection connection = null;
        try {
            connection = fetchConnection();
            PreparedStatement statement = getPreparedStatement(DbAccessStatement.SELECT_RESTAURANT_DESIGNATION,
                connection);
            LinkedList<String> restList = new LinkedList<String>();
            ResultSet rs = statement.executeQuery();
            while (rs.next()) {
                String restaurant = rs.getString("designation");
                restList.add(restaurant);
            }
            return restList;
        } catch (SQLException ex) {
            throw new DbAccessFailureException(ex.getMessage(), ex); // "Could not populate parameters of prepared statement"
        } finally {
            if (connection != null) {
                pool.releaseConnection(connection);
            }
        }
    }

    /**
     *
     * @return
     */
    private AdvancedConnection fetchConnection() {
        Exception cause;
        try {
            AdvancedConnection connection = pool.getConnection();
            return connection;
        } catch (NoConnectionAvailableException e) {
            cause = e;
        } catch (SQLException e) {
            cause = e;
        }
        throw new DbAccessFailureException(cause.getMessage(), cause);
    }

    private static PreparedStatement getPreparedStatement(DbAccessStatement statement, AdvancedConnection connection) {
        return getPreparedStatement(statement.getIdentifier(), connection);
    }

    private static PreparedStatement getPreparedStatement(Integer key, AdvancedConnection connection) {
        if (connection.containsStatement(key)) {
            return connection.getPreparedStatement(key);
        }
        RawStatement rawStatement = StatementStore.getInstance().getStatement(key);
        PreparedStatement statement;
        try {
            statement = connection.prepareStatement(rawStatement);
        } catch (SQLException e) {
            throw new DbAccessFailureException("Could not prepare statement with key: " + key, e);
        }
        return statement;
    }
}
